const sqlite3 = require('sqlite3').verbose();
const path = require("node:path");
const { getData } = require('./storage')
const fs = require('fs');
//缓存所在文件夹
// const DbDirPath = path.join(
//   process.env.LOCALAPPDATA || process.env.PROGRAMFILES,
//   "YuTian",
//   "YuTianPacs"
// );
let DbDirPath = undefined
const DbName = 'main.db'
global.db = undefined;


const selectDictList = async (device) => {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM ris_dict WHERE device = ?', [device], (err, rows) => {
      if (err) {
        reject('Error fetching data:', err.message);
      } else {
        resolve(rows)
      }
    })
  })
}

const selectDictValueList = async (dictId) => {
  return new Promise((resolve, reject) => {
    db.all('SELECT * FROM ris_dict_value WHERE dict_id = ?', [dictId], (err, rows) => {
      if (err) {
        reject('Error fetching data:', err.message);
      } else {
        resolve(rows)
      }
    })
  })
}



//准备初始数据
const checkedAndCreateTable = (tableName) => {
  return new Promise((resolve, reject) => {
    db.get(
      "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
      [tableName],
      (err, row) => {
        if (err) {
          reject('Error checking table existence:', err);
        }
        if (row) {
          resolve(true)
        } else {
          resolve(false)
        }
      }
    );
  })
}

//执行sql文件的同步方法
const execSql = (fileData) => {
  return new Promise((resolve, reject) => {
    db.exec(fileData, (err) => {
      if (err) {
        reject(err.message);
      } else {
        resolve()
      }
    });
  })
}

//初始化连接
const connectDB = () => {
  return new Promise((resolve, reject) => {
    //开始初始化
    db = new sqlite3.Database(path.join(DbDirPath, DbName), (err) => {
      if (err) {
        reject('Error connecting to database:', err.message);
      }
      db.run('CREATE TABLE IF NOT EXISTS patient (id VARCHAR PRIMARY KEY, bh VARCHAR, name VARCHAR, device VARCHAR, locate VARCHAR, gender VARCHAR, age VARCHAR, date VARCHAR, status VARCHAR, check_content TEXT, diagnose_content TEXT, printnum VARCHAR, report_time VARCHAR)');
      db.run('CREATE TABLE IF NOT EXISTS date_record (id INTEGER PRIMARY KEY AUTOINCREMENT, date VARCHAR)');
      resolve()
    });
  })
}

//初始化路径
const initPath = () => {
  return new Promise((resolve, reject) => {
    getData('dataPath').then((e) => {
      DbDirPath = e
      fs.access(DbDirPath, fs.constants.F_OK, (error) => {
        if (error) {
          //文件夹不存在
          fs.mkdir(DbDirPath, { recursive: true }, (mkdirErr) => {
            if (mkdirErr) {
              reject("创建数据库文件夹失败:" + mkdirErr)
              return;
            }
            resolve()
          });
          return
        }
        resolve()
      })
    })
  })
}

const clearDataBase = () => {
  return new Promise(resolve => {
    db.close(() => {
      try {
        fs.unlinkSync(path.join(DbDirPath, DbName), { recursive: true })
      } catch (err) {
        console.error('DB文件删除失败：', err);
      }
      resolve()
    })
  })
}

//通用插入数据
//need: tableName(表名) arg([data]数据集合)
const generalInsert = (tableName, data) => {
  return new Promise((resolve, reject) => {
    const valueStrs = []
    const values = []
    for (const attrName in data) {
      const v = data[attrName]
      if (!v) continue
      valueStrs.push(`${attrName}`)
      values.push(`'${v}'`)
    }
    const insert = db.prepare(`INSERT INTO ${tableName} ( ${valueStrs.join(',')} ) VALUES ( ${values.join(',')} )`)
    insert.run((err) => {
      insert.finalize();
      if (err) {
        reject('Error inserting data:', err.message);
      }
      resolve({ data })
    })
  })
}

//通用普通条件增量查询
//need: tableName(表名) arg([condition]查询条件) unique(是否只获取唯一结果)
const generalQuery = (tableName, condition, unique) => {
  return new Promise((resolve, reject) => {
    const queryStrs = []
    const querys = []
    //通用查询：防止全部查询
    if (!condition || !Object.keys(condition).length) {
      if (unique) {
        resolve({ row: undefined })
      }
      resolve({ rows: [] })
    }
    for (const attrName in condition) {
      const v = condition[attrName]
      if (!v) continue
      queryStrs.push(`${attrName} = ?`)
      querys.push(v)
    }
    db.all(`SELECT * FROM ${tableName} ${queryStrs.length ? 'WHERE' : ''} ${queryStrs.join('AND')} ${unique ? 'LIMIT 1 ' : ''}`, querys, (err, rows) => {
      if (err) {
        reject('Error fetching data:', err.message);
      } else {
        if (unique) {
          const row = rows.length ? rows[0] : undefined
          resolve({ row })
        }
        resolve({ rows })
      }
    })
  })
}

//通用普通条件增量查询数量
//need: tableName(表名) arg([condition]查询条件)
const generalQueryCount = (tableName, condition) => {
  return new Promise((resolve, reject) => {
    const queryStrs = []
    const querys = []
    for (const attrName in condition??{}) {
      const v = condition[attrName]
      if (!v) continue
      queryStrs.push(`${attrName} = ?`)
      querys.push(v)
    }
    db.get(`SELECT COUNT(*) AS count FROM ${tableName} ${queryStrs.length ? 'WHERE' : ''} ${queryStrs.join('AND')}`, querys, (err, row) => {
      if (err) {
        reject('Error fetching data:', err.message);
      } else {
        resolve(row.count)
      }
    })
  })
}

//通用普通条件增量分页查询
//need: tableName(表名) arg([condition]查询条件[currentPage]当前页[pageCount]总页)
const generalQueryPage = (tableName, arg) => {
  const { condition, currentPage, pageCount } = arg
  const queryStrs = []
  const querys = []
  for (const attrName in condition) {
    const v = condition[attrName]
    if (!v) continue
    queryStrs.push(`${attrName} = ?`)
    querys.push(condition[attrName])
  }
  const P1 = new Promise((resolve, reject) => {
    db.get(`SELECT COUNT(*) AS count FROM ${tableName} ${queryStrs.length ? 'WHERE' : ''} ${queryStrs.join('AND')}`, querys, (err, row) => {
      if (err) {
        reject('Error fetching data:', err.message);
      } else {
        resolve(row.count)
      }
    })
  })
  const P2 = new Promise((resolve, reject) => {
    db.all(`SELECT * FROM ${tableName} ${queryStrs.length ? 'WHERE' : ''} ${queryStrs.join('AND')} LIMIT ${currentPage},${pageCount}`, querys, (err, rows) => {
      if (err) {
        reject('Error fetching data:', err.message);
      } else {
        resolve(rows)
      }
    })
  })
  return Promise.all([P1, P2])
}

//通用修改数据
const generalUpdate = (tableName, arg) => {
  return new Promise((resolve, reject) => {
    const { data, id } = arg
    const valueStrs = []
    const values = []
    for (const attrName in data) {
      const v = data[attrName]
      if (!v) continue
      valueStrs.push(`${attrName} = ?`)
      values.push(data[attrName])
    }
    values.push(id)
    db.run(`UPDATE ${tableName} SET ${valueStrs.join(',')} WHERE id = ?`, values, function (err) {
      if (err) {
        reject('Error updating data:', err.message);
      }
      resolve()
    });
  })
}


module.exports = {
  async initDB() {
    //初始化路径
    await initPath()
    //初始化数据库
    await connectDB()
    //初始化默认表
    if (! await checkedAndCreateTable('ris_dict')) {
      const fileData = fs.readFileSync(path.join(__dirname, "../sql/ris_dict.sql"), 'utf8')
      await execSql(fileData)
    }
    if (! await checkedAndCreateTable('ris_dict_value')) {
      const fileData = fs.readFileSync(path.join(__dirname, "../sql/ris_dict_value.sql"), 'utf8')
      await execSql(fileData)
    }
  },
  selectDictList,
  selectDictValueList,
  clearDataBase,
  generalInsert,
  generalQuery,
  generalQueryCount,
  generalQueryPage,
  generalUpdate
}


